﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web.Management;

namespace dao.daos
{
    public class CompetenciaDao : BaseDao
    {
        public CompetenciaDao() : base("competencia", "id_competencia", true)
        {


        }

        public DataTable ObtenerCompetencias()
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = StringConexion;
            con.Open();
            DataTable dt = new DataTable();
            string sql =
                "select id_competencia, id_especialidad, distancia from competencia ";
            SqlCommand cmd = new SqlCommand(sql, con);
            dt.Load(cmd.ExecuteReader());
            return dt;

        }

        public DataTable ObtenerCompetenciasById(int idCom)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = StringConexion;
            con.Open();
            DataTable dt = new DataTable();
            string sql =
                "select id_especialidad, distancia from competencia where id_competencia = @idcom ";
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.Add(new SqlParameter("@idcom", idCom));
            dt.Load(cmd.ExecuteReader());
            return dt;

        }



        public void InsertarCompetenciaTorneo(int hora, DateTime fecha, int com, int tor)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = StringConexion;
            SqlTransaction trans = null;
            con.Open();
            trans = con.BeginTransaction();
            try
            {
                InsertarCompetenciaTorneo(hora, fecha, com, tor, con, trans);
                trans.Commit();
            }
            catch (Exception e)
            {
                if (trans != null)
                    trans.Rollback();

                throw new SqlExecutionException("Error al inscribir al nadador", e);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

        }
        public int InsertarCompetenciaTorneo(int hora, DateTime fecha, int com, int tor, SqlConnection cn, SqlTransaction trans)
        {
            string sql = "Insert into competencias_torneo (id_torneo, id_competencia, horario_inicio, mejor_tiempo_comp, mejor_nadador, fecha) values (@idtorneo, @IdCom, @hora, 1, 1, @fecha); select @@identity;";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add(new SqlParameter("@idtorneo", tor));
            cmd.Parameters.Add(new SqlParameter("@IdCom", com));
            cmd.Parameters.Add(new SqlParameter("@hora", hora));
            cmd.Parameters.Add(new SqlParameter("@fecha", fecha));
            return Convert.ToInt32(cmd.ExecuteScalar());

        }


        public void InscribirNadadorCompetencia(int idNad, int idCom, int idTor)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = StringConexion;
            SqlTransaction trans = null;
            con.Open();
            trans = con.BeginTransaction();
            try
            {
                InsertarNadCompTorneo(idNad, idCom, idTor, con, trans);
                trans.Commit();
            }
            catch (Exception e)
            {
                if (trans != null)
                    trans.Rollback();

                throw new SqlExecutionException("Error al inscribir al nadador", e);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

        }

        private void InsertarNadCompTorneo(int idNad, int idCom, int idTor, SqlConnection con, SqlTransaction trans)
        {
            string sql = "Insert into nadadores_x_competencia (id_competencia, id_nadador,id_torneo) values (@IdCom, @idnad,@idtorneo); select @@identity;";
            SqlCommand cmd = new SqlCommand(sql,con,trans);
            cmd.Parameters.Add(new SqlParameter("@idtorneo", idTor));
            cmd.Parameters.Add(new SqlParameter("@IdCom", idCom));
            cmd.Parameters.Add(new SqlParameter("@idnad", idNad));
            cmd.ExecuteNonQuery();
        }
    }
}
